package utils;

import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import net.sf.ezmorph.bean.MorphDynaBean;

import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.DynaProperty;
import org.apache.commons.beanutils.LazyDynaBean;
import org.apache.ddlutils.DatabaseOperationException;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.dynabean.SqlDynaBean;
import org.apache.ddlutils.model.Column;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;

import play.Logger;
import play.db.DB;
/**
 *
 * Warning: The test db must be HSQLDB, due to H2 is not support with DdlUtils.
 *
 * @author liura
 *
 */
public class DBHelper {
	private static ChineseSpelling finder = ChineseSpelling.getInstance();
	private static Map<String, Object> tableMap = new HashMap<String, Object>();
	private final static Map<Integer, Integer> FieldTypeMap = new HashMap<Integer, Integer>(){
		{
			put(0, Types.VARCHAR);
			put(1, Types.INTEGER);
			put(2, Types.FLOAT);
			put(3, Types.BOOLEAN);			
			put(4, Types.DATE);
		}
	};

	public static final Platform platform = PlatformFactory.createNewPlatformInstance(DB.datasource);

	public static void printDBstructure()	{
		Table[] tables = DBHelper.readDatabase().getTables();
		for (Table table : tables) {
			System.out.print("Table:"+table.getName());
			Column[] cols = table.getColumns();
			for (Column column : cols) {
				System.out.print("---"+column.getName());
				System.out.print("|"+column.getType()+"|"+column.getSize());
			}
			System.out.println();
		}
	}

	public static Map getTableMap()	{
		Table[] tables = DBHelper.readDatabase().getTables();
		for (Table table : tables) {
			String tableName = table.getName();
			tableMap.put(tableName, null);
		}
		return tableMap;
	}

	public static int getFieldTypeCode(Integer fieldType)	{
		return FieldTypeMap.get(fieldType);
	}

	public static void changeDatabase(Database targetModel, boolean alterDb) {
//		System.out.println("getAlterTablesSql() sql:"+platform.getAlterTablesSql(targetModel));
//		System.out.println("getCreateTablesSql() sql:"+platform.getCreateTablesSql(targetModel, true, true));
//		System.out.println("getDropTablesSql() sql:"+platform.getDropTablesSql(targetModel, true));
		if (alterDb) {
			platform.alterTables(targetModel, false);
		} else {
			platform.createTables(targetModel, false, true);
		}
	}

	public static Database readDatabase()	{
		String dbName=null;

		dbName = "play";//DB.getConnection().getMetaData().toString();

		System.out.println("dbName:"+dbName);
		Database db = platform.readModelFromDatabase(dbName);
	    return db;
	}

	/*
	 * 1. if no change, no update
	 * TODO: just add and update col, no delete
	 */
	public static void alterTable(DynaBean bean) {

		String tableName = (String) bean.get("tableName");// get table name
		System.out.println("tableName:" + tableName);
		Collection<MorphDynaBean> fieldList = (Collection<MorphDynaBean>) bean.get("fieldMapList");

		Database targetModel = DBHelper.readDatabase();
		Table t;
		if (!DBHelper.getTableMap().containsKey(tableName.toUpperCase())) {
			t = new Table();
			t.setName(tableName);
			// rid primary key
			Column c = new Column();
			c.setName("id");
			c.setTypeCode(Types.BIGINT);
			c.setPrimaryKey(true);
			c.setAutoIncrement(true);
			t.addColumn(c);
			
			// formId ref key
			Column fc = new Column();
			fc.setName("formId");
			fc.setTypeCode(Types.BIGINT);
			t.addColumn(fc);

			alterCols(fieldList, t);
			targetModel.addTable(t);
			System.out.print("add Table ");
		} else {
			t = targetModel.findTable(tableName.toUpperCase());
			alterCols(fieldList, t);
			System.out.print("add Column ");
		}

		boolean isAlterDb = false;
		if (DBHelper.getTableMap().containsKey(tableName.toUpperCase())) {
			isAlterDb = true;
		}
		System.out.println("changeDatabase....");
		DBHelper.changeDatabase(targetModel, isAlterDb);
	}

	private static void alterCols(Collection<MorphDynaBean> fieldList, Table t) {
		Column c;
		for (MorphDynaBean morphDynaBean : fieldList) {
			c = new Column();
//			DynaProperty[] dynaProperties = morphDynaBean.getDynaClass().getDynaProperties();
			String name = null;
			Integer type = (Integer)morphDynaBean.get("type");
			System.out.println(name+":"+type);
			if(type.equals("table")){
				DynaBean bean = (MorphDynaBean)morphDynaBean.get("refTable");
				alterTable(bean);
			}else{
				name = (String)morphDynaBean.get("name");
				if(name==null||name.length()==0){
					name = (String)morphDynaBean.get("caption");
					name = finder.getSelling(name);
				}
					
				c = t.findColumn(name);
				if (c == null) {
					c = new Column();
					c.setName(name);
					t.addColumn(c);
				}
				int intType = DBHelper.getFieldTypeCode(type);
				c.setTypeCode(intType);
				if(intType==Types.VARCHAR)
					c.setSize("255");
			}
		}
	}

	public static void saveData(DynaBean  dataBean, long formId){
		Database database = DBHelper.readDatabase();
	    // "t1" is a table of the model
		String tableName = (String)dataBean.get("tableName");

	    DynaBean record = (SqlDynaBean)database.createDynaBeanFor(tableName, false);
	    List<String> fieldList= (List<String>)dataBean.get("fieldList");
	    
	    List<List> rows= (List<List>)dataBean.get("valueList");
	    for (List row : rows) {	    	
	    	for (int i = 0; i < row.size(); i++) {
	    		record.set(fieldList.get(i).toUpperCase(), row.get(i));//    		
			}
	    	record.set("formId".toUpperCase(), formId);
	    	if(Long.valueOf((String) record.get("ID"))>0){
	    		System.out.println("update record:"+record);	
	    		platform.update(database, record);
	    	}else{
	    		System.out.println("insert record:"+record);
	    		platform.insert(database, record);
	    	}
	    	
	    	Logger.info("row:"+row);
	    }
	    //delete rows
	    List<String> delRows= (List<String>)dataBean.get("delValueList");
	    Logger.info("delRows:"+delRows.size());
	    for (String id : delRows) {	    	
	    	Logger.info("delRow ID:"+id);		
//	    	record.set("id", id);	
//		    platform.delete(database, record);
		}
	}

	public static List<String> getFieldList(String tableName){
		Logger.info("table ="+tableName);
		Database targetModel = DBHelper.readDatabase();
		Table t = targetModel.findTable(tableName.toUpperCase());
		List<String> fieldList = new ArrayList<String>();
		Column[] cols = t.getColumns();
		
		for (Column col : cols) {
			String colName = col.getName();
			fieldList.add(colName);
		}
		
		return fieldList;
	}
	
	public static List<List<Object>> getValueListByFormId(String tableName, long formId){
		
		System.out.println("getData from table:"+tableName);
		List<List<Object>> recordList = new ArrayList<List<Object>>();
		
		Database database = DBHelper.readDatabase();
		ArrayList params   = new ArrayList();
		params.add(formId);
		Iterator it = platform.query(database,
		                             "select * from "+tableName + " where formId = ?",
		                             params,
		                             new Table[] { database.findTable(tableName) });
		
		DynaProperty[] dynaProperties = null;
		while (it.hasNext()){		
			List<Object> valueList = new ArrayList<Object>();
			DynaBean rowBean = (DynaBean)it.next();
			if(dynaProperties == null)
		    	dynaProperties = rowBean.getDynaClass().getDynaProperties();
		    
		    for (DynaProperty dynaProperty : dynaProperties) {
		    	valueList.add(rowBean.get(dynaProperty.getName()));
			}
		    recordList.add(valueList);
		}
		
		return recordList;
	}
	
	public static String getData(String tableName, String id){
		try {
			System.out.println("getData from table:"+tableName);
			Database database = DBHelper.readDatabase();
			ArrayList params   = new ArrayList();
			params.add("Some title");
			Iterator it = platform.query(database,
			                             "select * from "+tableName,//where title = ? params,
			                             new Table[] { database.findTable(tableName) });
			//get table columns type

//			System.out.println("table name:" + bean.getDynaClass().getName());
//		String tableName = bean.getDynaClass().getName();
			DynaBean tableDefineBean = getTableDefineBean(tableName);

			DynaProperty[] dynaProperties = null;
			int i=0;
			while (it.hasNext())
			{
			    DynaBean rowBean = (DynaBean)it.next();
			    if(dynaProperties == null)
			    	dynaProperties = rowBean.getDynaClass().getDynaProperties();
			    List<Object> list = new ArrayList<Object>();
			    for (DynaProperty dynaProperty : dynaProperties) {
			    	list.add(rowBean.get(dynaProperty.getName()));
				}

			    tableDefineBean.set("valueList", i++, rowBean);//  list
			    System.out.println("rowBean:"+JsonHelper.getJsonFromBean(rowBean).toString());
//	        System.out.println(book.get("ID")+","+book.get("ORDERNO")+","+book.get("CREATORNAME")+","+book.get("CREATEDATE"));
			}
			//System.out.println(JsonHelper.getJsonFromBean(tableDefineBean).toString());
			return JsonHelper.getJsonFromBean(tableDefineBean).toString();
		} catch (DatabaseOperationException e) {
			e.printStackTrace();

		}
		return null;
	}
	public static String getData(DynaBean bean){
		try {
			DynaBean tableBean = (DynaBean)bean.get("TableDefine");
			String tableName = (String) tableBean.get("TableName");
			System.out.println("getData from table:"+tableName);
			Database database = DBHelper.readDatabase();
			ArrayList params   = new ArrayList();
			params.add("Some title");
			Iterator it = platform.query(database,
			                             "select * from "+tableName,//where title = ? params,
			                             new Table[] { database.findTable("t1") });
			//get table columns type

//			System.out.println("table name:" + bean.getDynaClass().getName());
//		String tableName = bean.getDynaClass().getName();
			DynaBean tableDefineBean = getTableDefineBean(tableName);

			DynaProperty[] dynaProperties = null;
			int i=0;
			while (it.hasNext())
			{
			    DynaBean rowBean = (DynaBean)it.next();
			    if(dynaProperties == null)
			    	dynaProperties = rowBean.getDynaClass().getDynaProperties();
			    List<Object> list = new ArrayList<Object>();
			    for (DynaProperty dynaProperty : dynaProperties) {
			    	list.add(rowBean.get(dynaProperty.getName()));
				}

			    tableDefineBean.set("valueList", i++, rowBean);//  list
			    System.out.println("rowBean:"+JsonHelper.getJsonFromBean(rowBean).toString());
//	        System.out.println(book.get("ID")+","+book.get("ORDERNO")+","+book.get("CREATORNAME")+","+book.get("CREATEDATE"));
			}
			//System.out.println(JsonHelper.getJsonFromBean(tableDefineBean).toString());
			return JsonHelper.getJsonFromBean(tableDefineBean).toString();
		} catch (DatabaseOperationException e) {
			e.printStackTrace();

		}
		return null;
	}

	public static void getDynaPropertyBeanFromTable(DynaBean bean) {
		DynaProperty[] dynaProperties = bean.getDynaClass().getDynaProperties();
		for (DynaProperty dynaProperty : dynaProperties) {
			System.out.println("dynaProperty.getType()= " + dynaProperty.getType());
			String propertyName = dynaProperty.getName();
			System.out.println(" " + propertyName + ":" + bean.get(propertyName));
		}
	}

	public static DynaBean getTableDefineBean(String tableName) {
		Database targetModel = DBHelper.readDatabase();
		Table t = targetModel.findTable(tableName.toUpperCase());
		DynaBean tableDefineBean = new LazyDynaBean();
		tableDefineBean.set("name", tableName);
		Column[] cols = t.getColumns();
		int i = 0;
		for (Column col : cols) {
			DynaBean colDefineBean = new LazyDynaBean();
			String colName = col.getName();
			String colType = col.getType();
			String colSize = col.getSize();
			colDefineBean.set("name",colName);
			colDefineBean.set("type",colType);
			colDefineBean.set("size",colSize);
//			System.out.println(" name:" + colName + ", type:" + colType);
			tableDefineBean.set("headerList", i++, colDefineBean);
		}
		return tableDefineBean;
	}

}
